/**
 * 
 */
package com.scpii.universal.util;

/**
 * UniversalApi
 * @author 甘建新
 * 2012-7-16下午03:40:19
 */
public class SQLs {
	//分页公共部分代码
	public static final String PUBLIC_STRS_HEAD = "SELECT * FROM (SELECT t.*,ROWNUM rn FROM ";
	public static final String PUBLIC_STRS_BACK = " t WHERE ROWNUM<=:MAXROWNUM) WHERE rn>:MINROWNUM";
	
	public static String GET_APP_PAGE = "SELECT * FROM APP_PAGE_TAB WHERE INT_APP_ID=:appId AND INT_PARENT_RESOURCE_ID=:pageId AND INT_DELETE_SIGN=0";
	//根据PAGEID，查询获得页面的标题
	public static String GET_APP_PAGE_TITLE ="SELECT * FROM PAGE_TITLE_TAB t1 WHERE t1.INT_DELETE_SIGN=0 AND t1.INT_TITLE_ID IN (SELECT t2.INT_TITLE_ID FROM TITLE_PAGE_TAB t2 WHERE t2.INT_RESOURCE_ID=:pageId AND INT_DELETE_SIGN=0)";
	public static String GET_PUSH_MSG_APPID="SELECT * FROM PUSH_MESSAGE_TAB p WHERE p.INT_APP_ID=:appId AND TO_DATE(DAT_PUSH_MSG_END_DATETIME,'YYYY-MM-DD HH24:MI:SS')>SYSDATE AND p.INT_DELETE_SIGN=0";
	
	//根据视图和页码范围，获取指定页码的数据
	public static String GET_VIEW_CATELOG_RELATION = PUBLIC_STRS_HEAD+" "+"(SELECT * FROM APP_VIEW_CATELOG_RALATION_TAB WHERE INT_VIEW_ID=:viewId AND INT_DELETE_SIGN=0 ORDER BY INT_SORT_ORDER DESC,INT_VIEW_CATE_ID ASC)"+" "+PUBLIC_STRS_BACK;
	//根据视图和页面范围，获得指定页码的数据列表
	public static String GET_VIEW_CONTENT_RELATION = PUBLIC_STRS_HEAD+" " +"(SELECT * FROM APP_VIEW_CONTENT_RALATION_TAB WHERE INT_VIEW_ID=:viewId AND INT_DELETE_SIGN=0 ORDER BY INT_SORT_ORDER DESC,INT_VIEW_CONT_ID ASC)" +" "+PUBLIC_STRS_BACK;
	//根据分类ID和应用ID，获取指定页码的数据列表
	public static String GET_PAGE_VIEW_CONTENT_LIST_BY_PAGE = PUBLIC_STRS_HEAD+" "+"(SELECT * FROM CONTENT_TAB WHERE INT_CATE_ID=:cateId AND INT_APP_ID=:appId AND INT_DELETE_SIGN=0 ORDER BY INT_CONTENT_ID ASC)"+" "+PUBLIC_STRS_BACK;
	//根据应用ID、用户ID，获取指定页面的反馈列表
	public static String GET_FEEDBACK_LIST_BY_PAGE = PUBLIC_STRS_HEAD+" "+"(SELECT * FROM FEEDBACK_TAB WHERE INT_APP_ID=:appId AND INT_USER_ID=:userId AND INT_DELETE_SIGN=0 AND INT_PARENT_FEEDBACK_ID=:intParentFeedbackId ORDER BY INT_FEEDBACK_ID ASC)"+" "+PUBLIC_STRS_BACK;
	//根据应用ID、用户ID，分页获取收藏列表
	public static String GET_FAVORITES_LIST_BY_PAGE = PUBLIC_STRS_HEAD+" "+"(SELECT * FROM FAVORITES_TAB WHERE INT_USER_ID=:userId AND INT_APP_ID=:appId AND INT_DELETE_SIGN=0 ORDER BY DAT_FAVORITE_DATETIME DESC)"+" "+PUBLIC_STRS_BACK;
	//根据用户ID、应用ID，分页获取订单列表
	public static String GET_ORDER_LIST_BY_PAGE =PUBLIC_STRS_HEAD+" "+"(SELECT * FROM ORDER_TAB WHERE INT_USER_ID=:userId AND INT_APP_ID=:appId AND INT_DELETE_SIGN=0 ORDER BY DAT_ORDER_DATETIME DESC)"+" "+PUBLIC_STRS_BACK;
	//根据应用ID，分页获取更多内容列表
	public static String GET_MORE_CONTENT_LIST_BY_PAGE = PUBLIC_STRS_HEAD+" "+"(SELECT * FROM MORE_CONTENT_TAB WHERE INT_APP_ID=:appId AND INT_RESOURCE_ID=:pageId AND INT_DELETE_SIGN=0 ORDER BY INT_ORDER ASC)"+" "+PUBLIC_STRS_BACK;
	//根据应用ID，分页获取推送的消息列表
	public static String GET_PUSH_MESSAGE_LIST_BY_PAGE = PUBLIC_STRS_HEAD+" "+"(SELECT * FROM PUSH_MESSAGE_TAB WHERE INT_APP_ID =:appId AND INT_PUSH_MSG_STATUE=0 AND TO_DATE(DAT_PUSH_MSG_END_DATETIME,'YYYY-MM-DD HH24:MI:SS')>SYSDATE AND INT_DELETE_SIGN=0 ORDER BY DAT_DAT_PUSH_MSG_DATETIME ASC)"+" "+PUBLIC_STRS_BACK;
}
